﻿create database ACCOMPRO
go
use ACCOMPRO
go

Create table Users
(
	UserId int identity(1,1) not null,
	Username varchar(15) not null,
	Pass varchar(15),
	Email varchar(15),
	Fullname nvarchar(50),
	Gender bit,
	Birthday datetime,
	Membertype nvarchar(15),
	PhoneNo char (12),
	Address nvarchar(50),
	RandomQuestion nvarchar(50),
	Answer nvarchar(50),
	
	constraint PK_User primary key (UserId),
	constraint chk_Gender check(Gender in(0,1)),
	constraint unique_username unique(username)
)
go

create table Post
(
	PostID int identity(1,1) not null,
	Owner varchar (15),
	Title nvarchar (100),
	Description nvarchar(100),
	IdHouse int,
	PostDate datetime,	
	
	
	
	constraint PK_Post primary key(PostID)				
)

--ALTER TABLE Post DROP CONSTRAINT PK_Post
--alter table Post Drop COLUMN  Email
--alter table Post Drop COLUMN  PostID
--alter table Post	ADD PostID varchar(100) not null
--alter table Post	Add constraint PK_Post primary key(PostID)
--alter table Post ALTER COLUMN Owner varchar (15) not null
	
	


go

create table City
(
	IdCity int IDENTITY(1,1)  not null,
	Name nvarchar(50),
	constraint PK_City primary key(IdCity)	
)


go

create table District
(
	IdDistrict int IDENTITY(1,1) not null,
	IdCity int not null,
	Name nvarchar(50),
	
	constraint PK_District primary key(IdDistrict)
)

--go 
--create table Renting
--(
--	Username varchar(15) not null,
--	House varchar(100),
--	DateOfRent datetime,
	
--	constraint PK_Renting primary key(Username, House,DateOfRent )
--)

go
create table BoardingHouse
(
	Owner varchar(15) not null,
	IdHouse int identity(1,1) not null,
	Street nvarchar(100),
	Ward nvarchar(50),
	District int,
	City int,
	Category int,
	
	Rating int,
	Contact char(15),
	
	
	constraint PK_BoardingHouse primary key(IdHouse)
)

go
create table Category
(
	IdCategory int IDENTITY(1,1) not null,
	Name nvarchar(50),
	
	
	constraint PK_Category primary key(IdCategory)
)



go
create table Rooms
(
	IdHouse int identity(1,1) not null,
	Name nvarchar(50),
	Descript nvarchar(500),
	Area int,
	Price int,
	MaxPerson int,
	CurPerson int,
	Internet bit,
	
	constraint PK_Rooms primary key(IdHouse)
	
)

--ALTER TABLE BoardingHouse DROP CONSTRAINT PK_BoardingHouse
--ALTER TABLE BoardingHouse add constraint PK_BoardingHouse primary key(IdHouse)

go

alter table Post add constraint FK_Post_BoardingHouse foreign key (IdHouse) references BoardingHouse(IdHouse)
--alter table Post add constraint FK_Post_Users foreign key (Owner) references Users(Username)
alter table BoardingHouse add constraint FK_BoardingHouse_Users foreign key (Owner) references Users(Username)
alter table BoardingHouse add constraint FK_BoardingHouse_City foreign key (City) references City(IdCity)
alter table BoardingHouse add constraint FK_BoardingHouse_District foreign key (District) references District(IdDistrict)
alter table District add constraint FK_District_City foreign key (IdCity) references City (IdCity)
--alter table Renting add constraint FK_Renting_BoardingHouse foreign key (House) references BoardingHouse(IdHouse)
--alter table Renting add constraint FK_Renting_Users foreign key (Username) references Users(Username)
alter table Rooms add constraint FK_Rooms_BoardingHouse foreign key (IdHouse) references BoardingHouse(IdHouse)
--alter table District add constraint FK_District_City foreign key (IdCity) references City(IdCity)
alter table BoardingHouse add constraint FK_BoardingHouse_Category foreign key (Category) references Category(IdCategory)



go 

insert into City values (N'Hồ Chí Minh')
insert into City values (N'Hà Nội')

go

insert into Users values ('Huy','123','h@gmail.com','DxHuy',1,'02-28-1994',N'Admin','123','123','025','qwe','your name','huy')
insert into Users values ('Thien','123','thien@gmail.com','Lhthien',1,'02-28-1994',N'Admin','123','123','025','qwe','your name','thien')
insert into Users values ('Phon','123','phon@gmail.com','Vphon',1,'02-28-1994',N'Admin','123','123','025','qwe','your name','phon')
insert into Users values ('Tin','123','tin@gmail.com','NLTtin',1,'02-28-1994',N'Admin','123','123','025','qwe','your name','tin')
insert into Users values ('nhaxinh','123','hoa@gmail.com',N'Nguyễn Ngọc Hoa',1,'01-28-1987',N'Chủ nhà','123','123','025','qwe','your name','hoa')
insert into Users values ('bedep','123','vi@gmail.com',N'Lê Phương Vi',1,'02-28-1990',N'Khách','123','123','025','qwe','your name','vi')
insert into Users values ('xinhxinh','123','khang@gmail.com',N'Đỗ Tường Khang',1,'02-28-1990',N'Khách','123','123','025','qwe','your name','khang')



CREATE PROCEDURE proUpdateUsers
(
@UserID int,
@strEmail varchar (100), 
@strFullname nvarchar (100),
@intGender bit, 
@dtBirthday datetime,
@strMemberType nvarchar (10),
@strPhoneNo char (12),
@strAddress nvarchar (100)
) 
AS 
UPDATE Users SET Email = @strEmail, Fullname = @strFullname, Gender = @intGender, Birthday = @dtBirthday, Membertype = @strMemberType, PhoneNo= @strPhoneNo, Address = @strAddress
where UserId = @UserID


